package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.Employee;

public class DBEmployee implements IFDBEmployee
{
    private Connection con;
    
    public DBEmployee() 
    {
        con = DBConnection.getInstance().getDBcon();
    }
  
    @Override
    public Employee findEmployee(int employeeID, boolean retriveAssociation)
    {
        Employee employeeObj = new Employee();
        employeeObj = singleWhere("eId = " + employeeID, retriveAssociation);
        
        return employeeObj;
    }
    
    @Override
    public Employee findEmployeeByName(String name, boolean retriveAssociation)
    {
        Employee employeeObj = new Employee();
        employeeObj = singleWhere("name = " + name, retriveAssociation);
        
        return employeeObj;
    }
    
    private Employee singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Employee employeeObj = new Employee();
        String query =  buildQuery(wClause);
        
        try
        {
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
           
	   if(results.next())
           {
	       employeeObj = buildEmployee(results);
           }
           
	   stmt.close();
	}
	catch(Exception e)
        {

        }
        
	return employeeObj;
    }
    
    private ArrayList<Employee> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Employee> list = new ArrayList<Employee>();

        String query =  buildQuery(wClause);
        
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while(results.next())
            {
                 Employee employeeObj = new Employee();
                 employeeObj = buildEmployee(results);	
                 list.add(employeeObj);
            }

            stmt.close();
        }
        catch(Exception e)
        {

        }

        return list;
    }
    
    private Employee buildEmployee(ResultSet results)
    {
        Employee employeeObj = new Employee();
        
        try
        {
           employeeObj.setEmployeeID(results.getInt(1)); // Skal resultaterne være i en bestemt rækkefølge, eller er det ligemeget?
           employeeObj.setAddress(results.getString(3)); 
           employeeObj.setName(results.getString(2));
           employeeObj.setSalary(results.getDouble(4));
           employeeObj.setStartDate(results.getString(5));
        }
        catch(Exception e)
        {

        }
        
        return employeeObj;
    }
 
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Employee";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query; 
    }

    @Override
    public ArrayList getAllEmployee(boolean retriveAssociation) 
    {
        ArrayList list = new ArrayList();
        list = miscWhere("", retriveAssociation);
        return list;
        
        // Manglende kode
    }

    @Override
    public void insertEmployee(Employee employeeObj)
    {  
        String query = "INSERT INTO Employee(name, address, salary, startDate) VALUES(" +
                "'" + employeeObj.getName() + "', " +
                "'"+ employeeObj.getAddress() + "', " +
                ""+ employeeObj.getSalary() + ", " +
                 "'"+ employeeObj.getStartDate() + "' )" ;
                    
        System.out.println("Update query: " + query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

    @Override
    public void updateEmployee(Employee employeeObj)
    {
        String query="UPDATE Employee SET "+
                "name = '" + employeeObj.getName() + "', " +
                "address ='"+ employeeObj.getAddress() + "', " +
                "salary ="+ employeeObj.getSalary() + ", " +
                "startDate ='"+ employeeObj.getStartDate() +"'   " + 
                " WHERE eId = '" + employeeObj.getEmployeeID() + "';";
        
        System.out.println("Update query: " + query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

    @Override
    public void deleteEmployee(int employeeID)
    {
        String query = "DELETE FROM Employee WHERE eId = '" +
                        employeeID + "'";

        try
        { 
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                stmt.executeUpdate(query);
                stmt.close();
        }
        catch(Exception ex)
        {

        }
    }

      @Override
    public int MaxID()
    {
        String query = "SELECT max(eID) FROM Employee";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading max id: " + e);
        } 
        return id;     
    }   
    
    
       @Override
    public int Antal() {
        String query = "SELECT COUNT(*) FROM Employee";
        ResultSet results;
        int id = -1;
        try
        {
            Statement stmt = con.createStatement();
            results = stmt.executeQuery(query);
            if( results.next())
            {
                id = results.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println("Query exception: Error in reading count: " + e);
        } 
        return id;   
    }
      
      
}
